﻿declare @date_update nvarchar(100)= (select [DATE_UPDATE] from [DATE_EM_REPORT])
declare @date_end_month nvarchar(100)= (select [MAX_DATE_ENDMONTH] from [DATE_EM_REPORT])
declare @month nvarchar(100)= (select [MAX_MONTH] from [DATE_EM_REPORT])

--select * from DATE_EM_REPORT
--select * from TBL_HR
--select * from TBL_BRANCH
--update TBL_HR_ACL set avatar = 'avatar.jpg' where avatar is null
--TBL_HR: danh sách nhân sự: update theo tháng
exec('
delete from tbl_hr_bk

insert tbl_hr_bk
select * from tbl_hr

delete from tbl_hr

insert TBL_HR
SELECT '''+@date_end_month+''' AS REPORT_DATE
,DAO
,[tên nhân viên] as DAO_NAME
, BRANCH_CODE AS BRANCH_CODE_SME
,[TRẠNG THÁI LÀM VIỆC: 1 LÀ ĐANG LÀM VIỆC,  0 LÀ HIỆN KHÔ] AS WORKING_STATUS
,[CHỨC DANH] AS POSITION_NAME
, [Mã vị trí] as POSITION
,[Ngày bắt đầu làm việc ở VPBank] as DATE_SALES_START
,Email
,LEFT(EMAIL,CHARINDEX(''@'',EMAIL)-1) AS USER_NAME
,case when [Mã vị trí] in (''smem'')  then ''3'' 
   when [Mã vị trí] in (''sbom'')  then ''3''
   when [Mã vị trí] in (''mbom'')  then ''3''
 else ''4'' end as GROUP_ID,
 ''avatar.jpg''
    --into TBL_HR
FROM HR_SME_'+@month+'

insert tbl_hr
select '''+@date_update+''', ''khong co DAO'', fullname, branchcode, 1, [POSITION], [POSITION], null, username, username, null, avatar
from TBL_HR_ACL a where NOT EXISTS (SELECT *
                   FROM   tbl_hr OD
                   WHERE  A.username = OD.USER_NAME)

update a 
set a.group_id = b.GROUP_ID
from tbl_hr as a, APPLICATION_GROUP as b where a.POSITION collate database_default=b.GROUP_NAME_LOCAL

update tbl_hr 
set BRANCH_CODE_SME = b.BRANCH_CODE_SME
from TBL_HR_ACL a, TBL_BRANCH b, tbl_hr c
where
a.username=c.USER_NAME and
c.BRANCH_CODE_SME = b.BRANCH_ID and
 a.branchcode = b.BRANCH_ID and a.position = ''branch''

 update tbl_hr
 set dao = ''khong co DAO'' where dao = ''Không có mã DAO''

 update tbl_hr
 set BRANCH_CODE_SME =''NTO''
 where BRANCH_CODE_SME = ''CLN''

 update tbl_hr
 set BRANCH_CODE_SME =''KLN''
 where BRANCH_CODE_SME = ''GVO''

update a
 set a.avatar = b.avatar
 from tbl_hr a, tbl_hr_bk b
 where a.dao <> ''khong co DAO'' and a.DAO = b.dao

 delete from tbl_hr where working_status = 0
')


--select * from tbl_hr where dao = '14725'
--select * from TBL_CUSTOMER a, TBL_HR b where a.DAO = b.DAO and  b.dao = '14725'
--select * from tbl_hr where USER_NAME = 'phuongdm5'
--select * from HR_SME_201704 where Email like 'phuongdm5%'


--select  * from HR_SME_201704 a, server16.[VPB_WHR2].[dbo].[TBL_DAO_CODE] b
--where a.[Mã nhân viên] = b.staff_id and b.staff_id = '17203'

--update a 
--set a.DAO = b.dao
--from HR_SME_201704 a, server16.[VPB_WHR2].[dbo].[TBL_DAO_CODE] b
--where a.[Mã nhân viên] = b.staff_id and b.staff_id = '17203'
--select * from TBL_HR  where group_id = '3'

--select * from TBL_BRANCH where BRANCH_NAME_SME = 'go vap'

--update tbl_hr 
--set BRANCH_CODE_SME = 'GVP', POSITION = 'MBOM', POSITION_NAME= N'GĐ KHDN vi mô' , group_id = 4
--where USER_NAME = 'minhnth2'

